USE ITViet
GO

-------------------------------------------------------------------------
--						@tblProduct
-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProduct_GetListProducts]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProduct_GetListProducts]
GO

CREATE PROCEDURE [dbo].[_tblProduct_GetListProducts]
AS
	SELECT 
		P.[Id],
		P.[Name],
		P.[CreatedDate],
		P.[CreatedBy],
		P.[ModifiedDate],
		P.[ModifiedBy],
		C.[Name] as Category
	FROM tblProduct P INNER JOIN tblProductCategory C ON p.[CategoryId]=C.[Id]
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProduct_GetModifiedByCategoryId]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProduct_GetModifiedByCategoryId]
GO

CREATE PROCEDURE [dbo].[_tblProduct_GetModifiedByCategoryId]
(
	@CatId int
)
AS
	SELECT 
		P.[Id],
		P.[Name],
		P.[CategoryId],
		P.[CreatedDate],
		P.[CreatedBy],
		P.[ModifiedDate],
		P.[ModifiedBy],
		P.[Image],
		P.Color,
		P.[HasInventory],
		P.[IsHot],
		C.[Name] as Category
	FROM tblProduct P 
		INNER JOIN tblProductCategory C ON p.[CategoryId]=C.[Id]
	WHERE (@CatId IS NULL OR @CatId=0 OR @CatId=C.[Id] OR @CatId = C.[ParentId])
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProduct_DeleteAllById]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProduct_DeleteAllById]
GO

CREATE PROCEDURE [dbo].[_tblProduct_DeleteAllById]
(
	@Id int
)
AS
	BEGIN TRAN
		DELETE tblProductLocale WHERE [ProductId]=@Id
		IF @@ERROR <> 0
			ROLLBACK TRAN
		DELETE tblProduct WHERE [Id]=@Id
		IF @@ERROR <> 0
			ROLLBACK TRAN
		COMMIT TRAN
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProduct_GetOtherProducts]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProduct_GetOtherProducts]
GO

CREATE PROCEDURE [dbo].[_tblProduct_GetOtherProducts]
(
	@Id			int,
	@Count	int,
	@LanguageId int
)
AS
	IF(@Count IS NULL OR @Count=0)
		BEGIN
			SELECT P.* FROM tblproduct P
			WHERE p.[Id]<>@Id
			ORDER BY P.[CreatedDate] DESC
		END
	ELSE
		BEGIN
			DECLARE @SQL varchar(2048)
			SET @SQL='SELECT TOP '+CAST(@Count AS VARCHAR(10))+' P.* FROM tblproduct P
								WHERE p.[Id]<>'+CAST(@Id AS VARCHAR(10))+' ORDER BY P.[CreatedDate] DESC'
			EXEC(@SQL)
		END 
	
GO

-------------------------------------------------------------------------
--						@tblProductLocale
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProductLocale_GetByCatLang]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProductLocale_GetByCatLang]
GO

CREATE PROCEDURE [dbo].[_tblProductLocale_GetByCatLang]
(
	@CatId	int,
	@LangId int
)
AS
	SELECT [RowCount] = ROW_NUMBER() OVER (ORDER BY P.[CreatedDate] DESC),
		P.[Id], P.[Code], P.[CategoryId], P.[Image], P.HasInventory,
		ISNULL(L.[Name],P.[Name]) AS [Name],
		L.[Description],
		L.[Tags],
		L.[RefPrice],
		L.[Content]
	FROM	tblProduct P LEFT JOIN tblProductLocale L 
				ON P.[Id]=L.[ProductId] AND L.[LanguageId]=@LangId
	WHERE	(P.[CategoryId]=@CatId)	
GO

-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProductLocale_GetTopHot]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProductLocale_GetTopHot]
GO

CREATE PROCEDURE [dbo].[_tblProductLocale_GetTopHot]
(
	@LangId int
)
AS
	SELECT TOP 3
		P.[Id], P.[Code], P.[CategoryId], P.[Image], P.HasInventory,
		ISNULL(L.[Name],P.[Name]) AS [Name],
		L.[Description],
		L.[Tags],
		L.[RefPrice],
		L.[Content]
	FROM tblProduct P 
		LEFT JOIN tblProductLocale L ON P.[Id]=L.[ProductId] AND L.[LanguageId]=@LangId
	WHERE P.[IsHot] = 1
	ORDER BY [CreatedDate] DESC
GO

-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProductLocale_GetAllInfoByProductId]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProductLocale_GetAllInfoByProductId]
GO

CREATE PROCEDURE [dbo].[_tblProductLocale_GetAllInfoByProductId]
(
	@LangId int,
	@ProductId int
)
AS
	SELECT
		P.[Id], P.[Code], P.[CategoryId], P.[Image], P.HasInventory, P.[Color], P.[Size],
		P.[AlbumId], P.[HasInventory], P.[IsHot], ISNULL(L.[Name],P.[Name]) AS [Name],
		L.[Description], L.[Tags], L.[RefPrice], L.[Content], L.[GuarantyPeriod], 
		L.[Material], L.[Original]
	FROM 
		(SELECT *
		FROM tblProduct
		WHERE [Id] = @ProductId) P 
		LEFT JOIN tblProductLocale L ON P.[Id]=L.[ProductId] AND L.[LanguageId]=@LangId
GO

-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProductLocale_GetOtherProducts]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProductLocale_GetOtherProducts]
GO

CREATE PROCEDURE [dbo].[_tblProductLocale_GetOtherProducts]
(
	@Id					int,
	@CategoryId int,
	@Count			int,
	@LanguageId int
)
AS
	IF(@Count IS NULL OR @Count=0)
		BEGIN
			SELECT P.*,PR.CategoryId 
			FROM tblproductLocale P INNER JOIN tblProduct PR ON P.[ProductId]=PR.[Id]
			WHERE P.[ProductId]<>@Id AND
						P.[LanguageId]=@LanguageId AND
						(@CategoryId IS NULL OR @CategoryId=0 OR @CategoryId=PR.[CategoryId])
			ORDER BY PR.[CreatedDate] DESC
		END
	ELSE
		BEGIN
			if(@CategoryId IS NULL)
				SET @CategoryId=0
			DECLARE @SQL varchar(2048)
			SET @SQL='SELECT TOP '+CAST(@Count AS VARCHAR(10))+' P.*,PR.CategoryId 
						FROM tblProductLocale P INNER JOIN tblProduct PR ON P.[ProductId]=PR.[Id]
						WHERE p.[Id]<>'+CAST(@Id AS VARCHAR(10))
						+' AND P.[LanguageId]='+CAST(@LanguageId AS VARCHAR(10))
						+' AND ('+CAST(@CategoryId AS VARCHAR(10))+'=0 '+' OR '+ CAST(@CategoryId AS VARCHAR(10))+'=PR.[CategoryId])'
						+' ORDER BY PR.[CreatedDate] DESC'
			EXEC(@SQL)
		END 
	
GO
-------------------------------------------------------------------------
-------------------------------------------------------------------------
--						@tblProductCategory
-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProductCategory_DeleteAllById]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProductCategory_DeleteAllById]
GO

CREATE PROCEDURE [dbo].[_tblProductCategory_DeleteAllById]
(
	@Id int
)
AS
	BEGIN TRAN
		DELETE tblProCategoryLocale WHERE [CategoryId]=@Id
		IF @@ERROR <> 0
			ROLLBACK TRAN
		DELETE tblProductCategory WHERE [Id]=@Id
		IF @@ERROR <> 0
			ROLLBACK TRAN
		COMMIT TRAN
GO
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProductCategory_GetByLang]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProductCategory_GetByLang]
GO

CREATE PROCEDURE [dbo].[_tblProductCategory_GetByLang]
(
	@langid	INT
)
AS
	SELECT DISTINCT
		P.[Id], P.[ParentId],
		ISNULL(L.Name, P.Name) AS [Name]
	FROM	tblProductCategory P 
				LEFT JOIN tblProCategoryLocale L ON L.CategoryId = P.Id AND L.LanguageId = @langid
GO
-------------------------------------------------------------------------
